package com.nccsjz.back.news.dao;

import java.sql.Clob;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import oracle.sql.CLOB;
import utils.DBUtils;

import com.nccsjz.base.BaseDAO;
import com.nccsjz.pojo.News;
import com.nccsjz.utils.DateUtil;
import com.nccsjz.utils.Page;
import com.nccsjz.utils.Pager;

/**
 * 新闻的CRUD操作
 * */
public class NewsDAO extends BaseDAO {

	private boolean flag;

	public NewsDAO(Connection con) {
		super(con);
	}

	// 添加新闻
	public boolean addNews(News news) throws SQLException {
		try {
			String sql = "insert into news(newstitle,newscontent,newsdate) values(?,?,?)";
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, news.getNewstitle());
			// pstmt.setString(2, news.getNewscontent());
			CLOB clob = DBUtils.getCLOBFromString(con, news.getNewscontent());
			pstmt.setClob(2, clob);
			if (null != news.getNewsdate()) {
				pstmt.setDate(3, DateUtil.convertDate(news.getNewsdate()));
			} else {
				pstmt.setDate(3, DateUtil.convertDate(new java.util.Date()));
			}
			int num = pstmt.executeUpdate();
			if (num != 0) {
				flag = true;
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			closeAll(rs, pstmt);
		}
		return flag;
	}

	// 修改新闻
	public boolean updateNews(News news) throws SQLException {
		try {
			String sql = "update news set newstitle=?,newscontent=? where newsid=?";
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, news.getNewstitle());
			// pstmt.setString(2, news.getNewscontent());
			CLOB clob = DBUtils.getCLOBFromString(con, news.getNewscontent());
			pstmt.setClob(2, clob);
			// pstmt.setDate(3, DateUtil.convertDate(news.getNewsdate()));
			pstmt.setLong(3, news.getNewsid());
			int num = pstmt.executeUpdate();
			if (num != 0) {
				flag = true;
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			closeAll(rs, pstmt);
		}
		return flag;
	}

	// 根据主键获取一条新闻
	public News getNews(Long id) throws SQLException {
		News news = null;
		try {

			String sql = "select * from news where newsid=?";
			pstmt = con.prepareStatement(sql);
			pstmt.setLong(1, id);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				news = new News();
				news.setNewsid(rs.getLong("newsid"));
				news.setNewstitle(rs.getString("newstitle"));
				// news.setNewscontent(rs.getString("newscontent"));
				CLOB clob = (CLOB) rs.getClob("newscontent");
				news.setNewscontent(DBUtils.getStringFromCLOB(clob));
				news.setNewsdate(rs.getDate("newsdate"));
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			closeAll(rs, pstmt);
		}
		return news;
	}

	// 根据主键删除新闻
	public boolean deleteNews(long id) throws SQLException {
		try {
			// conn=super.getConn();
			String sql = "delete from news where newsid=?";
			pstmt = con.prepareStatement(sql);
			pstmt.setLong(1, id);
			int num = pstmt.executeUpdate();
			if (num != 0) {
				flag = true;
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			closeAll(rs, pstmt);
		}
		return flag;
	}

	// 分页处理
	public Pager getNews(int pageSize, int pageNo) throws SQLException {
		List<News> list = new ArrayList<News>();
		int rownum = 0;
		try {
			String sql1 = "select count(*) from news";
			String sql2 = "select * from(select u.*,rownum rn from(select * from news order by newsdate desc) u where rownum<=?)where rn>?";

			pstmt = con.prepareStatement(sql1);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				rownum = rs.getInt(1);
			}
			pstmt = con.prepareStatement(sql2);
			pstmt.setInt(1, pageNo * pageSize);
			pstmt.setInt(2, (pageNo - 1) * pageSize);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				News news = new News();
				news.setNewsid(rs.getLong("newsid"));
				news.setNewstitle(rs.getString("newstitle"));
				// news.setNewscontent(rs.getString("newscontent"));
				CLOB clob = (CLOB) rs.getClob("newscontent");
				news.setNewscontent(DBUtils.getStringFromCLOB(clob));
				news.setNewsdate(rs.getDate("newsdate"));
				list.add(news);
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			closeAll(rs, pstmt);
		}
		Pager pager = new Pager(pageSize, pageNo, rownum, list);
		return pager;
	}

	// 批量删除
	public boolean deleteBathNews(List<Long> list) throws SQLException {
		int num[] = null;
		try {
			String sql = "delete from news where newsid=?";
			pstmt = con.prepareStatement(sql);
			for (int i = 0; i < list.size(); i++) {
				pstmt.setLong(1, list.get(i));
				pstmt.addBatch();
			}
			num = pstmt.executeBatch();
			if (num != null) {
				flag = true;
			}

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			closeAll(rs, pstmt);

		}
		return flag;
	}

	/**
	 * 得到前n条新闻。
	 * 
	 * @return
	 * @throws SQLException
	 */
	public List<News> getTopN(int n) throws SQLException {
		List<News> list = new ArrayList<News>();
		int rownum = 0;
		try {
			if (n <= 0) {
				throw new Exception("取得的记录条数不能为0或负数");
			}
			String sql2 = "select * from(select u.*,rownum rn from(select * from news order by newsid desc) u )where rn<=?";

			pstmt = con.prepareStatement(sql2);
			pstmt.setInt(1, n);

			rs = pstmt.executeQuery();
			while (rs.next() && rownum <= n) {
				News news = new News();
				news.setNewsid(rs.getLong("newsid"));
				news.setNewstitle(rs.getString("newstitle"));
				// news.setNewscontent(rs.getString("newscontent"));
				CLOB clob = (CLOB) rs.getClob("newscontent");
				news.setNewscontent(DBUtils.getStringFromCLOB(clob));
				news.setNewsdate(rs.getDate("newsdate"));
				list.add(news);
				rownum++;
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			closeAll(rs, pstmt);
		}
		return list;
	}

	/**
	 * getNewsPage获取新闻分页数据
	 * 
	 * 
	 * @param page
	 * @throws SQLException
	 */
	public void getNewsPage(Page page) throws SQLException {
		List<News> result = new ArrayList<News>();		
		String sql = "select u.NEWSID,u.NEWSTITLE,u.NEWSDATE " 
				+ " from (select row_number() over(order by NEWSID desc) rn,NEWSID,NEWSTITLE,NEWSDATE from "
				+ " NEWS) u where u.rn between ? and ?";		
		try {
			// 查询数据
			pstmt = con.prepareStatement(sql);			
			pstmt.setLong(1, page.getStartIndex());
			pstmt.setLong(2, page.getEndIndex());
			rs = pstmt.executeQuery();
			while (rs.next()) {				 
				News news = new News(rs.getLong(1),rs.getString(2),rs.getDate(3));
				result.add(news);
			}
			page.setData(result);
		} finally {
			closeAll(rs, pstmt);
		}
	}

	/**
	 * getRecordCount获得新闻记录总数
	 * 
	 * 
	 * @param page
	 * @return
	 * @throws SQLException
	 */
	public Long getRecordCount() throws SQLException {
		Long count = 0L;
		String sql = "select count(*) from NEWS";
		try {
			pstmt = con.prepareStatement(sql);			
			rs = pstmt.executeQuery();
			rs.next();
			count = rs.getLong(1);
		} finally {
			closeAll(rs, pstmt);
		}
		return count;
	}

}
